﻿using Microsoft.Data.Sqlite;
using software.model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace software.common
{
    public static class SqlReflection
    {
        /// <summary>
        /// sql语句对象替换，单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="info">对象</param>
        /// <returns></returns>
        public static string SetParamInfo(this string sql, object info)
        {
            try
            {
                //排序，长字段在上面这样避免属性前面字段重复出现导致错误替换的问题
                PropertyInfo[] shuxing = info.GetType().GetProperties().OrderByDescending(n => n.Name.Length).ToArray();
                foreach (PropertyInfo item in shuxing)
                {
                    string replaceText = "@" + ParseClassFieldFormat(item.Name);
                    if (sql.Contains(replaceText))
                    {
                        object value = item.GetValue(info, null);
                        if (item.PropertyType == typeof(string[]))
                        {
                            string[] fenge = (string[])item.GetValue(info, null);
                            string one = string.Join(",", fenge);
                            string two = one.Insert(0, "'").Replace(",", "','");
                            string three = two.Insert(two.Length, "'");
                            sql = sql.Replace(replaceText, three);
                        }
                        if (item.PropertyType == typeof(string) || item.PropertyType == typeof(int))
                        {
                            sql = sql.Replace(replaceText, value == null ? "null" : "'" + value.ToString() + "'");
                        }
                        if (item.PropertyType == typeof(DateTime))
                        {
                            sql = sql.Replace(replaceText, value == null ? "null" : "'" + value.ToString() + "'");
                        }
                    }
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// sql语句对象替换，多个对象 select * from table t left join info i where t.id=@t.Id(对象属性大小写一致) and i.name=@i.Name ,new string[] { "t", "i" }, person(t),student(i)
        /// 如果属性是string[]会变成string: 'a','b','c',集合类型待完善
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">表简称</param>
        /// <param name="info">对象 要表排序一致</param>
        /// <returns></returns>
        public static string SetParamInfo(this string sql, string[] param, params object[] info)
        {
            try
            {
                if (info.Length != param.Length)
                {
                    return "参数不匹配";
                }
                if (info.Length > 0)
                {
                    for (int i = 0; i < info.Length; i++)
                    {
                        //排序，长字段在上面这样避免属性前面字段重复出现导致错误替换的问题
                        PropertyInfo[] shuxing = info.GetType().GetProperties().OrderByDescending(n => n.Name.Length).ToArray();
                        foreach (PropertyInfo item in shuxing)
                        {

                            if (sql.Contains("@" + param[i] + "." + item.Name))
                            {
                                if (item.PropertyType == typeof(string[]))
                                {
                                    string[] fenge = (string[])item.GetValue(info[i], null);
                                    string one = string.Join(",", fenge);
                                    string two = one.Insert(0, "'").Replace(",", "','");
                                    string three = two.Insert(two.Length, "'");
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, three);
                                }
                                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(int))
                                {
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, "'" + (item.GetValue(info[i], null) ?? string.Empty).ToString() + "'");
                                }
                                if (item.PropertyType == typeof(DateTime))
                                {
                                    sql = sql.Replace("@" + param[i] + "." + item.Name, "'" + item.GetValue(info[i], null).ToString() + "'");
                                }
                            }
                        }

                    }
                    return sql;
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// table 装载对象 不区分大小写，适合单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns>单个对象</returns>
        public static T GetObjectInfo<T>(this SqliteDataReader sqlite) where T : new()
        {
            try
            {
                T objectmodel = new T();
                Type model = typeof(T);
                List<string> list = new List<string>();
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                List<TableColumn> columns = GetColumns<T>(sqlite, objectmodel);
                for (int i = 0; i < sqlite.FieldCount; i++)
                {

                    foreach (string columname in list)
                    {
                        if (sqlite[i] != null && sqlite[i].ToString() != "")
                        {
                            PropertyInfo infotype = model.GetProperty(ParseTableFieldFormat(columname), flag);
                            if (infotype.PropertyType == typeof(string))
                            {
                                infotype.SetValue(objectmodel, sqlite[i].ToString(), null);
                            }
                            else if (infotype.PropertyType == typeof(int))
                            {
                                infotype.SetValue(objectmodel, Convert.ToInt32(sqlite[i]), null);
                            }
                            else if (infotype.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(objectmodel, Convert.ToDateTime(sqlite[i]), null);
                            }
                        }

                    }


                }
                return objectmodel;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// table 装载对象 不区分大小写，适合返回多个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<T> GetObjectInfos<T>(this SqliteDataReader sqlite) where T : new()
        {
            try
            {
                T objectmodel = new T();
                List<T> listobject = new List<T>();
                Type model = typeof(T);
                List<string> list = new List<string>();
                List<TableColumn> columns = GetColumns<T>(sqlite, objectmodel);
                //读取数据
                while (sqlite.Read())
                {
                    T obj = new T();//使用开辟新的堆空间，否则他们的栈会指向同一个堆空间，导致对象覆盖
                    foreach (TableColumn column in columns)
                    {
                        if (sqlite[column.ColumnName] != null && sqlite[column.ColumnName].ToString() != "")
                        {
                            PropertyInfo infotype = column.PropertyValue;
                            if (column.PropertyType == typeof(string))
                            {
                                infotype.SetValue(obj, sqlite[column.ColumnName].ToString(), null);
                            }
                            else if (column.PropertyType == typeof(int))
                            {
                                infotype.SetValue(obj, Convert.ToInt32(sqlite[column.ColumnName]), null);
                            }
                            else if (column.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(obj, Convert.ToDateTime(sqlite[column.ColumnName]), null);
                            }
                        }
                    }
                    listobject.Add(obj);
                }
                return listobject;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// table 装载对象 不区分大小写，适合单个对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns>单个对象</returns>
        public static T GetObjectInfo<T>(this SqlDataReader sqlite) where T : new()
        {
            try
            {
                T objectmodel = new T();
                Type model = typeof(T);
                List<string> list = new List<string>();
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                List<TableColumn> columns = GetColumns<T>(sqlite, objectmodel);
                for (int i = 0; i < sqlite.FieldCount; i++)
                {

                    foreach (string columname in list)
                    {
                        if (sqlite[i] != null && sqlite[i].ToString() != "")
                        {
                            PropertyInfo infotype = model.GetProperty(ParseTableFieldFormat(columname), flag);
                            if (infotype.PropertyType == typeof(string))
                            {
                                infotype.SetValue(objectmodel, sqlite[i].ToString(), null);
                            }
                            else if (infotype.PropertyType == typeof(int))
                            {
                                infotype.SetValue(objectmodel, Convert.ToInt32(sqlite[i]), null);
                            }
                            else if (infotype.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(objectmodel, Convert.ToDateTime(sqlite[i]), null);
                            }
                        }

                    }


                }
                return objectmodel;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// table 装载对象 不区分大小写，适合返回多个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="o"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<T> GetObjectInfos<T>(this SqlDataReader sqlite) where T : new()
        {
            try
            {
                T objectmodel = new T();
                List<T> listobject = new List<T>();
                Type model = typeof(T);
                List<string> list = new List<string>();
                List<TableColumn> columns = GetColumns<T>(sqlite, objectmodel);
                //读取数据
                while (sqlite.Read())
                {
                    T obj = new T();//使用开辟新的堆空间，否则他们的栈会指向同一个堆空间，导致对象覆盖
                    foreach (TableColumn column in columns)
                    {
                        if (sqlite[column.ColumnName] != null && sqlite[column.ColumnName].ToString() != "")
                        {
                            PropertyInfo infotype = column.PropertyValue;
                            if (column.PropertyType == typeof(string))
                            {
                                infotype.SetValue(obj, sqlite[column.ColumnName].ToString(), null);
                            }
                            else if (column.PropertyType == typeof(int))
                            {
                                infotype.SetValue(obj, Convert.ToInt32(sqlite[column.ColumnName]), null);
                            }
                            else if (column.PropertyType == typeof(DateTime))
                            {
                                infotype.SetValue(obj, Convert.ToDateTime(sqlite[column.ColumnName]), null);
                            }
                        }
                    }
                    listobject.Add(obj);
                }
                return listobject;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 判断属性是否在对象中存在 区分大小写
        /// </summary>
        /// <param name="instance"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static bool ContainProperty(this object instance, string propertyName)
        {
            if (instance != null && !string.IsNullOrEmpty(propertyName))
            {
                PropertyInfo _findedPropertyInfo = instance.GetType().GetProperty(propertyName);
                return (_findedPropertyInfo != null);
            }
            return false;
        }
        /// <summary>
        /// 判断属性是否在对象中存在 不区分大小写
        /// </summary>
        /// <param name="instance"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static bool ContainPropertyAll(this object instance, string propertyName)
        {
            BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
            if (instance != null && !string.IsNullOrEmpty(propertyName))
            {
                PropertyInfo _findedPropertyInfo = instance.GetType().GetProperty(propertyName, flag);
                return (_findedPropertyInfo != null);
            }
            return false;
        }
        /// <summary>
        /// 字段格式化,表字段转实体类
        /// </summary>
        /// <param name="columName"></param>
        /// <returns></returns>
        public static string ParseTableFieldFormat(string columName)
        {
            string[] chars = columName.Split('_');
            string result = "";
            for (int i = 0; i < chars.Length; i++)
            {
                string name = chars[i];
                result += Char.ToUpper(name[0]) + name.Substring(1);
            }
            return result;
        }
        /// <summary>
        /// 字段格式化,实体类转表字段
        /// </summary>
        /// <param name="columName"></param>
        /// <returns></returns>
        public static string ParseClassFieldFormat(string propertyName)
        {
            string result = "";
            MatchCollection match = Regex.Matches(propertyName, "([A-Z]+(?=$|[A-Z][a-z]|[0-9])|([A-Z]?[a-z]+[0-9]+)|([A-Z]?[a-z]+))");
            for (int i = 0; i < match.Count; i++)
            {
                string name = match[i].Value;
                result += Char.ToLower(name[0]) + name.Substring(1) + "_";
            }
            result = result.Substring(0, result.Length - 1);
            return result;
        }
        private static List<TableColumn> GetColumns<T>(SqliteDataReader sqlite, T model) where T : new()
        {
            List<TableColumn> columns = new List<TableColumn>();
            Type modelType = typeof(T);
            for (int i = 0; i < sqlite.FieldCount; i++)
            {
                TableColumn tableColumn = new TableColumn();
                tableColumn.ColumnName = sqlite.GetName(i);
                tableColumn.ColumnType = sqlite.GetFieldType(i);
                //获取class属性名称
                string propertyName = ParseTableFieldFormat(tableColumn.ColumnName);
                //当查询结果列在类属性中有字段对应时，添加进集合
                if (model.ContainPropertyAll(propertyName))
                {
                    //获取属性字段信息
                    tableColumn.PropertyName = propertyName;
                    BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                    PropertyInfo infotype = modelType.GetProperty(propertyName, flag);
                    tableColumn.PropertyType = infotype.PropertyType;
                    tableColumn.PropertyValue = infotype;
                    columns.Add(tableColumn);
                }
            }
            return columns;
        }
        private static List<TableColumn> GetColumns<T>(SqlDataReader sqlite, T model) where T : new()
        {
            List<TableColumn> columns = new List<TableColumn>();
            Type modelType = typeof(T);
            for (int i = 0; i < sqlite.FieldCount; i++)
            {
                TableColumn tableColumn = new TableColumn();
                tableColumn.ColumnName = sqlite.GetName(i);
                tableColumn.ColumnType = sqlite.GetFieldType(i);
                //获取class属性名称
                string propertyName = ParseTableFieldFormat(tableColumn.ColumnName);
                //当查询结果列在类属性中有字段对应时，添加进集合
                if (model.ContainPropertyAll(propertyName))
                {
                    //获取属性字段信息
                    tableColumn.PropertyName = propertyName;
                    BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                    PropertyInfo infotype = modelType.GetProperty(propertyName, flag);
                    tableColumn.PropertyType = infotype.PropertyType;
                    tableColumn.PropertyValue = infotype;
                    columns.Add(tableColumn);
                }
            }
            return columns;
        }

        public static DataTable ToDataTable(this SqliteDataReader dataReader)
        {
            ///定义DataTable
            DataTable datatable = new DataTable();

            try
            {    ///动态添加表的数据列
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    DataColumn myDataColumn = new DataColumn();
                    myDataColumn.DataType = dataReader.GetFieldType(i);
                    myDataColumn.ColumnName = dataReader.GetName(i);
                    datatable.Columns.Add(myDataColumn);
                }

                ///添加表的数据
                while (dataReader.Read())
                {
                    DataRow myDataRow = datatable.NewRow();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        myDataRow[i] = dataReader[i].ToString();
                    }
                    datatable.Rows.Add(myDataRow);
                    myDataRow = null;
                }
                ///关闭数据读取器
                dataReader.Close();
                return datatable;
            }
            catch (Exception ex)
            {
                ///抛出类型转换错误
                //SystemError.CreateErrorLog(ex.Message);
                throw new Exception(ex.Message, ex);
            }
        }
    }
}